﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using CWB.Common.Logging;
using CWB.Common.Utilities;
using CWB.Web.CacheLayer;
using CWB.Common.DTO;
using CWB.Web.Models;
using System.Linq;
using CWB.Common.Constant;
using System.Runtime.Serialization;

namespace CWB.Web.DB
{
    [DataContract]
    public class DbModelSentence
    {
        [DataMember(Name = "sn")]
        public int sn;
        [DataMember(Name = "ss")]
        public string Sentence;
        [DataMember(Name = "v")]
        public string VoiceWord;
        [DataMember(Name = "s")]
        public int status;
        [DataMember(Name = "csn")]
        public int CategorySn;
        [DataMember(Name = "so")]
        public int SortingWeight;
    }

    public class DbTableSentence : BaseDAO
    {
        private readonly static ISysLog log = SysLog.GetLogger(typeof(DbTableSentence));

        public DbTableSentence()
        {
            base.init(typeof(DbTableSentence).ToString(), DataBaseName.Sentence);
        }

        private List<DbModelSentence> getModuleCallBack(SQLiteDataReader sdr)
        {
            List<DbModelSentence> listD = new List<DbModelSentence>() { };
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    listD.Add(new DbModelSentence()
                    {
                        sn = Convert.ToInt32(sdr["sn"].ToString()),
                        Sentence = sdr["Sentence"].ToString(),
                        VoiceWord = sdr["VoiceWord"].ToString(),
                        status = Convert.ToInt32(sdr["status"].ToString()),
                        CategorySn = Convert.ToInt32(sdr["categorySn"].ToString()),
                        SortingWeight = Convert.ToInt32(sdr["SortingWeight"].ToString()),
                    });
                }
            }
            return listD;
        }

        public List<DbModelSentence> getDataByParentSN(ref Paging pg, int parentSN)
        {
            List<KeyValuePair<string, string>> paras = new List<KeyValuePair<string, string>>() { };
            KeyValuePair<string, string> searchPara = new KeyValuePair<string, string>("categorySn", parentSN.ToString());
            paras.Add(searchPara);
            DBSortingPara dbSortingPara = new DBSortingPara();
            dbSortingPara.AddSortingItem("Sentence");
            return getAllData(ref pg, paras, dbSortingPara);
        }

        public DbModelSentence getDataBySn(int sn)
        {
            const string strCMD = "select * from LearnCht where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            List<DbModelSentence> rtn = ExecuteReader<DbModelSentence>(CommandType.Text, strCMD, listPara, getModuleCallBack);
            if (rtn.Count > 0)
            {
                return rtn[0];
            }
            else
            {
                Exception e = new Exception(string.Format("Data not found. {0}.sn : [{1}]", typeof(DbTableSentence).Name, sn));
                throw (e);
            }
        }

        public bool CheckWordIsExist(string word)
        {
            int nums = ExecuteReaderCount<string>("LearnCht", "Sentence", word);
            if (nums == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool createData(DbModelSentence model)
        {
            const string strCMD = "insert into LearnCht (Sentence, VoiceWord, status, categorySn) values (@Sentence, @VoiceWord, @status, @categorySn)";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@Sentence", model.Sentence));
            listPara.Add(new SQLiteParameter("@VoiceWord", model.VoiceWord));
            listPara.Add(new SQLiteParameter("@status", model.status));
            listPara.Add(new SQLiteParameter("@categorySn", model.CategorySn));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            if (icnt > 0)
            {
                const string strSort = "update LearnCht set SortingWeight = sn where Sentence = @Sentence";
                listPara = new List<SQLiteParameter>() { };
                listPara.Add(new SQLiteParameter("@Sentence", model.Sentence));
                icnt = ExecuteNonQuery(strSort, listPara);
            }

            return (icnt > 0);
        }

        public bool updateDataBySN(DbModelSentence model)
        {
            //check is there are data existing.
            string commandText = "Select count(*) as rcnt from LearnCht where Sentence = @Sentence and sn!= @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@Sentence", model.Sentence));
            listPara.Add(new SQLiteParameter("@sn", model.sn));
            int icnt = ExecuteReaderCount(CommandType.Text, commandText, listPara);
            if (icnt == 0)
            {
                commandText = "Update LearnCht set Sentence = @Sentence, VoiceWord = @VoiceWord, status = @status, categorySn = @categorySn where sn = @sn";
                listPara.Add(new SQLiteParameter("@Sentence", model.Sentence));
                listPara.Add(new SQLiteParameter("@VoiceWord", model.VoiceWord));
                listPara.Add(new SQLiteParameter("@status", model.status));
                listPara.Add(new SQLiteParameter("@sn", model.sn));
                listPara.Add(new SQLiteParameter("@categorySn", model.CategorySn));
                icnt = ExecuteNonQuery(commandText, listPara);
                return (icnt > 0);
            }
            else
            {
                throw new Exception(string.Format("The same Sentence ({0})is exist.", model.Sentence));
            }
        }

        public void ReSortingOrdering(int SortingFrom, int SortingTo, int SentenceSn, int NewSortingID)
        {
            //Get old SortingID;
            DbModelSentence model = getDataBySn(SentenceSn);
            int gape = (NewSortingID > model.SortingWeight) ? -1 : 1;

            string commandText = string.Format("update LearnCht set SortingWeight = SortingWeight + {0} where SortingWeight >= {1} and SortingWeight <= {2}", gape, SortingFrom, SortingTo);
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            int icnt = ExecuteReaderCount(CommandType.Text, commandText, listPara);

            commandText = string.Format("update LearnCht set SortingWeight = {0} where sn = {1}", NewSortingID, SentenceSn);
            icnt = ExecuteReaderCount(CommandType.Text, commandText, listPara);
        }

        public bool DeleteDataBySn(int sn)
        {
            const string strCMD = "delete from LearnCht where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public List<DbModelSentence> getAllData(ref Paging pg, List<KeyValuePair<string, string>> SearchParas, DBSortingPara dbSortingPara)
        {
            string sqlMain = "Select * from LearnCht";
            string sqlWehre = " Where 1 = 1 ";
            string sqlOrderby = string.Empty;
            List<SQLiteParameter> sqlPara = new List<SQLiteParameter>() { };

            foreach (KeyValuePair<string, string> pair in SearchParas)
            {
                if (!string.IsNullOrEmpty(pair.Key) && !string.IsNullOrEmpty(pair.Value))
                {
                    if (string.Compare("categorySn", pair.Key, true) == 0)
                    {
                        if (string.Compare(pair.Value, "0", true) != 0)
                        {
                            sqlWehre += " and categorySn = @categorySn";
                            sqlPara.Add(new SQLiteParameter("categorySn", pair.Value));
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else
                    {
                        string key = pair.Key;
                        sqlWehre += string.Format("and {0} = @{1}", key, key);
                        sqlPara.Add(new SQLiteParameter(string.Format("@{0}", key), pair.Value));
                    }
                }
            }

            sqlOrderby = dbSortingPara.getOrderByHLQ();

            //get totalRecord
            string sqlCount = string.Format("select count(*) as rcnt from {0}", sqlMain.Split(' ').Last());
            string strCMD = string.Format("{0} {1}", sqlCount, sqlWehre);
            int count = ExecuteReaderCount(strCMD, sqlPara);
            pg.totalRecord = count;

            //get data
            strCMD = string.Format("{0} {1} {2} {3}", sqlMain, sqlWehre, sqlOrderby, pg.getLimitSql());
            List<DbModelSentence> listData = ExecuteReader<DbModelSentence>(CommandType.Text, strCMD, sqlPara, getModuleCallBack);

            return listData;
        }
    }
}
